﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using CarLoanSystem.Entity;

namespace CarLoanSystem.DAL
{
    public class BondsmanInfoDAL : DBBase
    {
        public static DataTable LoadAllBondsman(int id = 0)
        {
            try
            {
                string sqlQuery = @"SELECT [BONDSMAN_ID]
                                              ,[BONDSMAN_NAME]
                                              ,[BONDSMAN_LASTNAME]
                                              ,[BONDSMAN_PERSONAL_ID]
                                              ,[BONDSMAN_AGE]
                                              ,[BONDSMAN_TEL]
                                              ,[BONDSMAN_ADDRESS]
                                              ,[BONDSMAN_SARARY]
                                              ,[BONDSMAN_WORK_ADDRESS]
                                              ,[BONDSMAN_WORK_TEL]
                                              ,[BONDSMAN_JOB]
                                              ,[CUSTOMER_ID]
                                          FROM [CAR_LOAN].[dbo].[BONDSMAN] where [CUSTOMER_ID] = " + id;
                DBBase.ConncetDatabase();

                SqlCommand command = new SqlCommand(sqlQuery, DBBase.con);
                SqlDataReader dr = command.ExecuteReader();

                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                    DBBase.DisConncetDatabase();
                    return dt;
                }
                else
                {
                    DBBase.DisConncetDatabase();
                    return null;
                }

            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public static bool check(string id)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlDataReader dr;
                SqlCommand command = new SqlCommand(@"SELECT [BONDSMAN_PERSONAL_ID]
                   FROM [CAR_LOAN].[dbo].[BONDSMAN] WHERE [BONDSMAN_PERSONAL_ID] = @BONDSMAN_PERSONAL_ID", DBBase.con);
                command.Parameters.AddWithValue("@BONDSMAN_PERSONAL_ID", id);
                dr = command.ExecuteReader();
                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                }
                DBBase.DisConncetDatabase();
                if (dt.Rows.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public static BondsmanItem LoadBondsman(int id)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlDataReader dr;
                SqlCommand command = new SqlCommand(@"SELECT [BONDSMAN_ID]
                                              ,[BONDSMAN_NAME]
                                              ,[BONDSMAN_LASTNAME]
                                              ,[BONDSMAN_PERSONAL_ID]
                                              ,[BONDSMAN_AGE]
                                              ,[BONDSMAN_TEL]
                                              ,[BONDSMAN_ADDRESS]
                                              ,[BONDSMAN_SARARY]
                                              ,[BONDSMAN_WORK_ADDRESS]
                                              ,[BONDSMAN_WORK_TEL]
                                              ,[BONDSMAN_JOB]
                                              ,[CUSTOMER_ID]
                                          FROM [CAR_LOAN].[dbo].[BONDSMAN] WHERE [BONDSMAN_ID] = @BONDSMAN_ID", DBBase.con);
                command.Parameters.AddWithValue("@BONDSMAN_ID", id);
                dr = command.ExecuteReader();
                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                }
                DBBase.DisConncetDatabase();
                if (dt.Rows.Count > 0)
                {
                    BondsmanItem item = new BondsmanItem();
                    item.CUSTOMER_ID = Convert.ToInt32(dt.Rows[0]["CUSTOMER_ID"].ToString());
                    item.BONDSMAN_ID = Convert.ToInt32(dt.Rows[0]["BONDSMAN_ID"].ToString());
                    item.BONDSMAN_NAME = dt.Rows[0]["BONDSMAN_NAME"].ToString();
                    item.BONDSMAN_LASTNAME = dt.Rows[0]["BONDSMAN_LASTNAME"].ToString();
                    item.BONDSMAN_PERSONAL_ID = dt.Rows[0]["BONDSMAN_PERSONAL_ID"].ToString();
                    item.BONDSMAN_AGE = Convert.ToInt32(dt.Rows[0]["BONDSMAN_AGE"].ToString());
                    item.BONDSMAN_TEL = dt.Rows[0]["BONDSMAN_TEL"].ToString();
                    item.BONDSMAN_ADDRESS = dt.Rows[0]["BONDSMAN_ADDRESS"].ToString();
                    item.BONDSMAN_SARARY = Convert.ToInt32(dt.Rows[0]["BONDSMAN_SARARY"].ToString());
                    item.BONDSMAN_WORK_ADDRESS = dt.Rows[0]["BONDSMAN_WORK_ADDRESS"].ToString();
                    item.BONDSMAN_WORK_TEL = dt.Rows[0]["BONDSMAN_WORK_TEL"].ToString();
                    item.BONDSMAN_JOB = dt.Rows[0]["BONDSMAN_JOB"].ToString();
                    return item;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public static int LoadBondsmanID(int id)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlDataReader dr;
                SqlCommand command = new SqlCommand(@"SELECT [BONDSMAN_ID]
                FROM [CAR_LOAN].[dbo].[BONDSMAN] WHERE [CUSTOMER_ID] = @CUSTOMER_ID", DBBase.con);
                command.Parameters.AddWithValue("@CUSTOMER_ID", id);
                dr = command.ExecuteReader();
                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                }
                DBBase.DisConncetDatabase();
                if (dt.Rows.Count > 0)
                {
                    return Convert.ToInt32(dt.Rows[0]["BONDSMAN_ID"].ToString());
                }
                else
                {
                    return 0;
                }
            }
            catch (Exception ex)
            {
                return 0;
            }
        }

        public static BondsmanItem LoadBondsmanByCusID(int id)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlDataReader dr;
                SqlCommand command = new SqlCommand(@"SELECT [BONDSMAN_ID]
                                              ,[BONDSMAN_NAME]
                                              ,[BONDSMAN_LASTNAME]
                                              ,[BONDSMAN_PERSONAL_ID]
                                              ,[BONDSMAN_AGE]
                                              ,[BONDSMAN_TEL]
                                              ,[BONDSMAN_ADDRESS]
                                              ,[BONDSMAN_SARARY]
                                              ,[BONDSMAN_WORK_ADDRESS]
                                              ,[BONDSMAN_WORK_TEL]
                                              ,[BONDSMAN_JOB]
                                              ,[CHECK_IDENTITY_CARD]
                                              ,[CHECK_REGISTRATION]
                                              ,[CHECK_SALARY_SLIP]
                                              ,[CHECK_ACCOUNT]
                                              ,[CUSTOMER_ID]
                FROM [CAR_LOAN].[dbo].[BONDSMAN] WHERE [CUSTOMER_ID] = @CUSTOMER_ID", DBBase.con);
                command.Parameters.AddWithValue("@CUSTOMER_ID", id);
                dr = command.ExecuteReader();
                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                }
                DBBase.DisConncetDatabase();
                if (dt.Rows.Count > 0)
                {
                    BondsmanItem item = new BondsmanItem();
                    item.CUSTOMER_ID = Convert.ToInt32(dt.Rows[0]["CUSTOMER_ID"].ToString());
                    item.BONDSMAN_ID = Convert.ToInt32(dt.Rows[0]["BONDSMAN_ID"].ToString());
                    item.BONDSMAN_NAME = dt.Rows[0]["BONDSMAN_NAME"].ToString();
                    item.BONDSMAN_LASTNAME = dt.Rows[0]["BONDSMAN_LASTNAME"].ToString();
                    item.BONDSMAN_PERSONAL_ID = dt.Rows[0]["BONDSMAN_PERSONAL_ID"].ToString();
                    item.BONDSMAN_AGE = Convert.ToInt32(dt.Rows[0]["BONDSMAN_AGE"].ToString());
                    item.BONDSMAN_TEL = dt.Rows[0]["BONDSMAN_TEL"].ToString();
                    item.BONDSMAN_ADDRESS = dt.Rows[0]["BONDSMAN_ADDRESS"].ToString();
                    item.BONDSMAN_SARARY = Convert.ToInt32(dt.Rows[0]["BONDSMAN_SARARY"].ToString());
                    item.BONDSMAN_WORK_ADDRESS = dt.Rows[0]["BONDSMAN_WORK_ADDRESS"].ToString();
                    item.BONDSMAN_WORK_TEL = dt.Rows[0]["BONDSMAN_WORK_TEL"].ToString();
                    item.CHECK_IDENTITY_CARD = dt.Rows[0]["CHECK_IDENTITY_CARD"].ToString();
                    item.CHECK_REGISTRATION = dt.Rows[0]["CHECK_REGISTRATION"].ToString();
                    item.CHECK_SALARY_SLIP = dt.Rows[0]["CHECK_SALARY_SLIP"].ToString();
                    item.CHECK_ACCOUNT = dt.Rows[0]["CHECK_ACCOUNT"].ToString();
                    item.BONDSMAN_JOB = dt.Rows[0]["BONDSMAN_JOB"].ToString();
                    return item;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public static bool Insert(BondsmanItem item)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlCommand command = new SqlCommand(@"INSERT INTO [CAR_LOAN].[dbo].[BONDSMAN]
                                                       ([BONDSMAN_NAME]
                                                       ,[BONDSMAN_LASTNAME]
                                                       ,[BONDSMAN_PERSONAL_ID]
                                                       ,[BONDSMAN_AGE]
                                                       ,[BONDSMAN_TEL]
                                                       ,[BONDSMAN_ADDRESS]
                                                       ,[BONDSMAN_SARARY]
                                                       ,[BONDSMAN_WORK_ADDRESS]
                                                       ,[BONDSMAN_WORK_TEL]
                                                       ,[BONDSMAN_JOB]
                                                       ,[CUSTOMER_ID])
                                                 VALUES
                                                      ( @BONDSMAN_NAME
                                                       ,@BONDSMAN_LASTNAME
                                                       ,@BONDSMAN_PERSONAL_ID
                                                       ,@BONDSMAN_AGE
                                                       ,@BONDSMAN_TEL
                                                       ,@BONDSMAN_ADDRESS
                                                       ,@BONDSMAN_SARARY
                                                       ,@BONDSMAN_WORK_ADDRESS
                                                       ,@BONDSMAN_WORK_TEL
                                                       ,@BONDSMAN_JOB
                                                       ,@CUSTOMER_ID)", DBBase.con);

                command.Parameters.AddWithValue("@BONDSMAN_NAME", item.BONDSMAN_NAME);
                command.Parameters.AddWithValue("@BONDSMAN_LASTNAME", item.BONDSMAN_LASTNAME);
                command.Parameters.AddWithValue("@BONDSMAN_PERSONAL_ID", item.BONDSMAN_PERSONAL_ID);
                command.Parameters.AddWithValue("@BONDSMAN_AGE", item.BONDSMAN_AGE);
                command.Parameters.AddWithValue("@BONDSMAN_TEL", item.BONDSMAN_TEL);
                command.Parameters.AddWithValue("@BONDSMAN_ADDRESS", item.BONDSMAN_ADDRESS);
                command.Parameters.AddWithValue("@BONDSMAN_SARARY", item.BONDSMAN_SARARY);
                command.Parameters.AddWithValue("@BONDSMAN_WORK_ADDRESS", item.BONDSMAN_WORK_ADDRESS);
                command.Parameters.AddWithValue("@BONDSMAN_WORK_TEL", item.BONDSMAN_WORK_TEL);
                command.Parameters.AddWithValue("@BONDSMAN_JOB", item.BONDSMAN_JOB);
                command.Parameters.AddWithValue("@CUSTOMER_ID", item.CUSTOMER_ID);
                int tmp = command.ExecuteNonQuery();
                DBBase.DisConncetDatabase();
                return true;
            }
            catch (Exception ex)
            {
                DBBase.DisConncetDatabase();
                throw ex;
            }
        }

        public static bool Update(BondsmanItem item)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlCommand command = new SqlCommand(@"UPDATE [CAR_LOAN].[dbo].[BONDSMAN]
                                               SET [BONDSMAN_NAME] = @BONDSMAN_NAME
                                                  ,[BONDSMAN_LASTNAME] = @BONDSMAN_LASTNAME
                                                  ,[BONDSMAN_PERSONAL_ID] = @BONDSMAN_PERSONAL_ID
                                                  ,[BONDSMAN_AGE] = @BONDSMAN_AGE
                                                  ,[BONDSMAN_TEL] = @BONDSMAN_TEL
                                                  ,[BONDSMAN_ADDRESS] = @BONDSMAN_ADDRESS
                                                  ,[BONDSMAN_SARARY] = @BONDSMAN_SARARY
                                                  ,[BONDSMAN_WORK_ADDRESS] = @BONDSMAN_WORK_ADDRESS
                                                  ,[BONDSMAN_WORK_TEL] = @BONDSMAN_WORK_TEL
                                                  ,[BONDSMAN_JOB] = @BONDSMAN_JOB
                                                  ,[CUSTOMER_ID] = @CUSTOMER_ID
                                             WHERE [BONDSMAN_ID] = @BONDSMAN_ID", DBBase.con);

                command.Parameters.AddWithValue("@BONDSMAN_ID", item.BONDSMAN_ID);
                command.Parameters.AddWithValue("@BONDSMAN_NAME", item.BONDSMAN_NAME);
                command.Parameters.AddWithValue("@BONDSMAN_LASTNAME", item.BONDSMAN_LASTNAME);
                command.Parameters.AddWithValue("@BONDSMAN_PERSONAL_ID", item.BONDSMAN_PERSONAL_ID);
                command.Parameters.AddWithValue("@BONDSMAN_AGE", item.BONDSMAN_AGE);
                command.Parameters.AddWithValue("@BONDSMAN_TEL", item.BONDSMAN_TEL);
                command.Parameters.AddWithValue("@BONDSMAN_ADDRESS", item.BONDSMAN_ADDRESS);
                command.Parameters.AddWithValue("@BONDSMAN_SARARY", item.BONDSMAN_SARARY);
                command.Parameters.AddWithValue("@BONDSMAN_WORK_ADDRESS", item.BONDSMAN_WORK_ADDRESS);
                command.Parameters.AddWithValue("@BONDSMAN_WORK_TEL", item.BONDSMAN_WORK_TEL);
                command.Parameters.AddWithValue("@BONDSMAN_JOB", item.BONDSMAN_JOB);
                command.Parameters.AddWithValue("@CUSTOMER_ID", item.CUSTOMER_ID);
                int tmp = command.ExecuteNonQuery();
                DBBase.DisConncetDatabase();
                return true;
            }
            catch (Exception ex)
            {
                DBBase.DisConncetDatabase();
                throw ex;
            }
        }

        public static bool UpdateSaraly(BondsmanItem item)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlCommand command = new SqlCommand(@"UPDATE [CAR_LOAN].[dbo].[BONDSMAN]
                                               SET [BONDSMAN_SARARY] = @BONDSMAN_SARARY
                                                  ,[CHECK_IDENTITY_CARD] = @CHECK_IDENTITY_CARD
                                                  ,[CHECK_REGISTRATION] = @CHECK_REGISTRATION
                                                  ,[CHECK_SALARY_SLIP] = @CHECK_SALARY_SLIP
                                                  ,[CHECK_ACCOUNT] = @CHECK_ACCOUNT
                                             WHERE [BONDSMAN_ID] = @BONDSMAN_ID", DBBase.con);

                command.Parameters.AddWithValue("@BONDSMAN_ID", item.BONDSMAN_ID);
                command.Parameters.AddWithValue("@BONDSMAN_SARARY", item.BONDSMAN_SARARY);
                command.Parameters.AddWithValue("@CHECK_IDENTITY_CARD", item.CHECK_IDENTITY_CARD);
                command.Parameters.AddWithValue("@CHECK_REGISTRATION", item.CHECK_REGISTRATION);
                command.Parameters.AddWithValue("@CHECK_SALARY_SLIP", item.CHECK_SALARY_SLIP);
                command.Parameters.AddWithValue("@CHECK_ACCOUNT", item.CHECK_ACCOUNT);
                int tmp = command.ExecuteNonQuery();
                DBBase.DisConncetDatabase();
                return true;
            }
            catch (Exception ex)
            {
                DBBase.DisConncetDatabase();
                throw ex;
            }
        }

        public static void Delete(int id)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlCommand command = new SqlCommand(@"DELETE FROM [CAR_LOAN].[dbo].[BONDSMAN]
                                                    WHERE CUSTOMER_ID = @CUSTOMER_ID", DBBase.con);
                command.Parameters.AddWithValue("@CUSTOMER_ID", id);
                int tmp = command.ExecuteNonQuery();
                DBBase.DisConncetDatabase();
            }
            catch (Exception ex)
            {
                DBBase.DisConncetDatabase();
                throw ex;
            }
        }
    }
}
